package com.augurit.gzcgwsp.smartformCsdlwj.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.transaction.annotation.Transactional;

import com.augurit.common.base.dao.BaseDao;
import com.augurit.common.util.lang.DateUtils;

/**
 * 表单自定义dao基类
 * @author zhangjun
 *
 */
public class SmartformBaseDao<T,Pk> extends BaseDao<T,Long>{
	private static final Logger log = Logger.getLogger(SmartformBaseDao.class);
	
	private static String path = "";

	public List getResultList(String sql) {
		List list = null;
		if (sql != null && sql.trim().length() > 0) {
			Session session = this.getSession();
			SQLQuery query = session.createSQLQuery(sql);
			list = query.list();
		}
		return list;
	}
	/**
	 * 执行sql语句
	 * 
	 * @throws SQLException
	 * @throws SQLException
	 */
	@Transactional
	public void executeSql(String sql) {

		if (sql != null && sql.trim().length() > 0) {
			Session session = this.getSession();
			SQLQuery query = session.createSQLQuery(sql);
			query.executeUpdate();
		}
	}
	/**
	 * sql修改某张表
	 * @param sql
	 * @return
	 */
	public  int updateDateBase(String sql) {
		logSQLBefor(sql);
		Connection conn = null;
		Statement st = null;
		int num = 0;
		try {
			long timeBefore = System.currentTimeMillis();
			conn = getConnection();
			st = getStatement(conn);
			num = st.executeUpdate(sql);
			logSQLAfter(sql,  timeBefore);
		}catch(Exception e) {
			log.error("更错误", e);
		}finally {
			free(conn, st, null);
		}
		
		return num;
	}
	
	
	/**
	 * 简单SQL查询  
	 * 
	 * @param sql
	 * @return
	 */
	public  List queryForList(String sql) {
		logSQLBefor(sql);
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<Map> tempList = new ArrayList<Map>();
		try {
			long timeBefore = System.currentTimeMillis();
			conn = getConnection();
			st = getStatement(conn);
			rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();
			while(rs.next()) {
				Map<String, String> map = new LinkedHashMap<String, String>();
				for(int i=1; i<=columnCount; i++) {
					String colName = meta.getColumnLabel(i);
					colName=colName.toLowerCase();
					Object value=rs.getObject(i);
					if(value instanceof Timestamp || value instanceof Date){
                        Date date = (Date)value;
                        map.put(colName, DateUtils.dateTimeToString(date, DateUtils.YEAR_MOUTH_DAY_));
                    }else if(value instanceof String){
                    	String colValue=rs.getString(i);
                    	if(StringUtils.isNotBlank(colValue))
                    		map.put(colName, colValue);
                    	else
                    		map.put(colName, "");
                    }else{
                    	String colValue=rs.getString(i);
                    	map.put(colName, colValue);
                    }
				}
				tempList.add(map);
			}
			
			logSQLAfter(sql,  timeBefore);
		}catch(Exception e) {
			log.error("查找异常", e);
		}finally {
			free(conn, st, rs);
		}
		
		return tempList;
	}
	
	/*
	 * 获取Statement，并且设置最长的查询时间，超过该时间会自动抛出异常
	 */
	private  Statement getStatement(Connection conn) throws Exception {
		Statement t = conn.createStatement();
		return t;
	}
	/**  
	 * 关闭连接资源  
	 * @param conn  
	 */
	public static void free(Connection conn, Statement st, ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/*
	 * 日志记录SQL执行前的信息
	 */
	public static void logSQLBefor(String sql) {
		StackTraceElement[] ste = new Throwable().getStackTrace();
		StringBuffer CallStack = new StringBuffer();
		for (int i = 0; i < ste.length; i++) {
			CallStack.append(ste[i].toString() + " | ");
			if (i == 6)
				break;
		}
		ste = null;
		path = CallStack.toString();
		log.info("执行路径：" + path);
		log.info("执行脚本：" + sql);
	}
	
	
	@SuppressWarnings("deprecation")
	public  Connection getConnection() {
		Connection conn = null;
		try {
			conn=getSession().connection();
		} catch (HibernateException e) {
			log.error("查询统计数据连接错误"+e);
		}
		
		return conn;
	}
	
	
	/*
	 * 日志记录SQL执行后的信息
	 */
	public static void logSQLAfter(String sql,  long timeBefore) {
		long timeDiff = System.currentTimeMillis() - timeBefore;
		String timeDiffResult = timeDiff < 1000 ? timeDiff + "毫秒!" : (timeDiff / 1000) + "秒钟!";
		log.info("执行时间：" + timeDiffResult);
	}
	
}
